package com.hc.excel.util;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;

import com.hc.bean.GradeBean;
import com.hc.comm.CommData;

public class CreateALLDataToExcelToDisk {


	public static boolean createALLDataToExcel(Map<String,List> data,String filePath,String startDate,String stopDate){

		// TODO Auto-generated method stub
		try {
			InputStream in = new FileInputStream(CommData.systemPath+"ExcelFile\\test.xls");
			Workbook work = new HSSFWorkbook(in);
			
			
			for(String key:data.keySet()){
				
				List list = data.get(key);
				if("webTest".equals(key)){
					Sheet sheet = work.getSheetAt(0);
					CreateWebTestDataSheet(sheet,list,startDate,stopDate);
				}else if("downFileTest".equals(key)){
					Sheet sheet = work.getSheetAt(1);
					CreateDownFileTestDataSheet(sheet,list,startDate,stopDate);
					
				}else if("company".equals(key)){
					Sheet sheet = work.getSheetAt(2);
					CreateCompanyGradeDataSheet(sheet,list,startDate,stopDate);
					
				} else if("vlan".equals(key)){
					Sheet sheet = work.getSheetAt(3);
					CreateVlanGradeDataSheet(sheet,list,startDate,stopDate);
					
				}
				
			}
			
			FileOutputStream os = new FileOutputStream(CommData.excelTempFilePath+"\\"+filePath+".xls");
			work.write(os);
			os.close();
		} catch (FileNotFoundException e) {
			System.out.println("文件路径错误");
			e.printStackTrace();
			return false;
		} catch (IOException e) {
			System.out.println("文件输入流错误");
			e.printStackTrace();
			return false;
		}

	
		return true;
	}

	
	
	
	private static void CreateVlanGradeDataSheet(Sheet sheet, List<GradeBean> data,
			String startDate, String stopDate) {
		Row row = null;
		

		for (int i = 0;i < data.size(); i++) {
			
			row = sheet.createRow((int) i+11);
			GradeBean bean =  data.get(i);
			// 第四步，创建单元格，并设置值
			row.createCell((short) 0).setCellValue(bean.getVlanId()+"");
			row.createCell((short) 1).setCellValue(bean.getWebtimedelayGrade()+"");
			row.createCell((short) 2).setCellValue(bean.getWebShakeGrade()+"");
			row.createCell((short) 3).setCellValue(bean.getWeblostRateGrade()+"");
			row.createCell((short) 4).setCellValue(bean.getWebGrade()+"");
			row.createCell((short) 5).setCellValue(bean.getWebTestCount()+"");
			row.createCell((short) 6).setCellValue(bean.getDownFileGrade()+"");
			row.createCell((short) 7).setCellValue(bean.getDownFileTestCount()+"");
			row.createCell((short) 8).setCellValue(bean.getGradeCount()+"");
			row.createCell((short) 9).setCellValue(bean.getbSizeQuality()+"");

		}
		//创建每个单元格，添加样式，最后合并
		row = sheet.getRow(5);
		
		Cell cell = row.getCell(1);
		cell.setCellValue(startDate);// 填充值
		
		
		row = sheet.getRow(6);
		 cell = row.getCell(1);
		cell.setCellValue(stopDate);// 填充值
		
		
	}




	private static void CreateCompanyGradeDataSheet(Sheet sheet, List<GradeBean> data,
			String startDate, String stopDate) {
		
		Row row= null;
		int len = 11+data.size();
		// 得到行，并填充数据和表格样式
		for (int i = 0;i < data.size(); i++) {
			
			row = sheet.createRow((int) i+11);
			GradeBean bean =  data.get(i);
			// 第四步，创建单元格，并设置值
			row.createCell((short) 0).setCellValue(bean.getVlanId()+"");
			row.createCell((short) 1).setCellValue(bean.getWebtimedelayGrade()+"");
			row.createCell((short) 2).setCellValue(bean.getWebShakeGrade()+"");
			row.createCell((short) 3).setCellValue(bean.getWeblostRateGrade()+"");
			row.createCell((short) 4).setCellValue(bean.getWebGrade()+"");
			row.createCell((short) 5).setCellValue(bean.getWebTestCount()+"");
			row.createCell((short) 6).setCellValue(bean.getDownFileGrade()+"");
			row.createCell((short) 7).setCellValue(bean.getDownFileTestCount()+"");
			row.createCell((short) 8).setCellValue(bean.getGradeCount()+"");
			row.createCell((short) 9).setCellValue(bean.getbSizeQuality()+"");
			
	

		
		}
		//创建每个单元格，添加样式，最后合并
		row = sheet.getRow(5);
		
		Cell cell = row.getCell(1);
		cell.setCellValue(startDate);// 填充值
		
		
		row = sheet.getRow(6);
		 cell = row.getCell(1);
		cell.setCellValue(stopDate);// 填充值
		
		
	}




	private static void CreateDownFileTestDataSheet(Sheet sheet, List data,String startDate, String stopDate) {
		
		
		
		Row row = null;
		

		// 得到行，并填充数据和表格样式
		for (int i = 0;i < data.size(); i++) {
			
			row = sheet.createRow((int) i+11);
			Map map = (Map) data.get(i);
			// 第四步，创建单元格，并设置值
			row.createCell((short) 0).setCellValue(map.get("userNumber").toString());
			row.createCell((short) 1).setCellValue(map.get("intranetIp").toString());
			row.createCell((short) 2).setCellValue(map.get("externalIp").toString());
			row.createCell((short) 3).setCellValue(map.get("bSize").toString());
			row.createCell((short) 4).setCellValue(map.get("vlanId1").toString());
			row.createCell((short) 5).setCellValue(map.get("vlanId2").toString());
			row.createCell((short) 6).setCellValue(map.get("speedMax").toString());
			row.createCell((short) 7).setCellValue(map.get("speedAvr").toString());
			row.createCell((short) 8).setCellValue(map.get("speedMin").toString());
			row.createCell((short) 9).setCellValue(map.get("cmdTime").toString());
	
		}
		//创建每个单元格，添加样式，最后合并
		row = sheet.getRow(5);
		
		Cell cell = row.getCell(1);
		cell.setCellValue(startDate);// 填充值
		
		
		row = sheet.getRow(6);
		 cell = row.getCell(1);
		cell.setCellValue(stopDate);// 填充值
		
		
		
	}




	private static void CreateWebTestDataSheet(Sheet sheet, List data,
			String startDate, String stopDate) {
		Row row = null;
		for (int i = 0;i < data.size(); i++) {
			
			row = sheet.createRow((int) i+11);
			Map map = (Map) data.get(i);
			// 第四步，创建单元格，并设置值
			row.createCell((short) 0).setCellValue(map.get("userNumber").toString());
			row.createCell((short) 1).setCellValue(map.get("intranetIp").toString());
			row.createCell((short) 2).setCellValue(map.get("externalIp").toString());
			row.createCell((short) 3).setCellValue(map.get("bSize").toString());
			row.createCell((short) 4).setCellValue(map.get("vlanId1").toString());
			row.createCell((short) 5).setCellValue(map.get("vlanId2").toString());
			row.createCell((short) 6).setCellValue(map.get("url").toString());
			row.createCell((short) 7).setCellValue(map.get("testCount").toString());
			row.createCell((short) 8).setCellValue(map.get("timedelayMax").toString());
			row.createCell((short) 9).setCellValue(map.get("timedelayAvr").toString());
			row.createCell((short) 10).setCellValue(map.get("timedelayMin").toString());
			row.createCell((short) 11).setCellValue(map.get("shake").toString());
			row.createCell((short) 12).setCellValue(map.get("lostRate").toString());
			row.createCell((short) 13).setCellValue(map.get("cmdTime").toString());
	
//			row = sheet.createRow(i);// 得到行
//			cell = row.createCell(0);// 得到第0个单元格
//			cell.setCellValue("琳"+i);// 填充值
//			cell.setCellStyle(columnOne);// 填充样式
//			cell = row.createCell(1);
//			cell.setCellValue("女");
//			cell.setCellStyle(columnOne);// 填充样式
//			cell = row.createCell(2);
//			cell.setCellValue(i+20);
//			cell.setCellStyle(columnOne);// 填充样式
		
		}
		//创建每个单元格，添加样式，最后合并
		row = sheet.getRow(5);
		
		Cell cell = row.getCell(1);
		cell.setCellValue(startDate);// 填充值
		
		
		row = sheet.getRow(6);
		 cell = row.getCell(1);
		cell.setCellValue(stopDate);// 填充值
		
		

		
	}


	

}
